<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class M_surat_kontrol extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, tgl_surat, 
		status_jaminan, tgl_klaim, kd_klaim FROM surat_jaminan 
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		WHERE klaim.jenis = "kontrol" AND id_petugas_loket = "'.user_id().'" AND tgl_surat = CURDATE()
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_cari($keyword) {
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, tgl_surat, 
		status_jaminan, tgl_klaim, kd_klaim FROM surat_jaminan 
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		WHERE klaim.jenis = "kontrol" AND id_petugas_loket = "'.user_id().'"
		AND (surat_jaminan.kd_surat_jaminan like "%'.$keyword.'%" 
		OR penduduk.nama like "%'.$keyword.'%" 
		OR penduduk.nik like "%'.$keyword.'%")
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_cari_tanggal($keyword,$tgl_awal,$tgl_akhir) {
		$sql = 'SELECT klaim.kd_surat_jaminan, nama, jk, alamat, jenis_kunjungan, tgl_surat, 
		status_jaminan, tgl_klaim, kd_klaim FROM surat_jaminan 
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN master_jenis_kunjungan ON surat_jaminan.id_jenis_kunjungan = master_jenis_kunjungan.id_jenis_kunjungan
		WHERE klaim.jenis = "kontrol" AND id_petugas_loket = "'.user_id().'"
		AND (surat_jaminan.kd_surat_jaminan like "%'.$keyword.'%" 
		OR penduduk.nama like "%'.$keyword.'%" 
		OR penduduk.nik like "%'.$keyword.'%")
		AND tgl_surat BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
		ORDER BY klaim.tgl_surat, surat_jaminan.kd_surat_jaminan DESC';
		return $sql;
	}

	function get_export() {
		$sql = 'SELECT * FROM surat_jaminan';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function save_klaim($dataInsert) {
		$query = $this->db->insert('klaim', $dataInsert);
		return $query;
	}

	function get_by($params) {
		$sql = 'SELECT surat_jaminan.kd_surat_jaminan, id_jenis_kunjungan, nomer_surat, tgl_surat, 
		surat_jaminan.kd_ppk, ppk, kd_rujukan, kd_klaim, diagnosa_lain,
		penduduk.nik, nama, jk , keluarga.alamat, kelas_perawatan, surat_jaminan.keterangan,
		keterangan_verifikator, status_jaminan, ttd, first_name, last_name, nip, diagnosa_lain, 
		tempat_lahir, tgl_lahir, (MONTH(tgl_surat)-MONTH(tgl_lahir)) AS umur FROM surat_jaminan
		LEFT JOIN peserta ON surat_jaminan.kd_peserta = peserta.kd_peserta
		LEFT JOIN penduduk ON peserta.nik = penduduk.nik
		LEFT JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		LEFT JOIN master_kelas_perawatan ON surat_jaminan.id_kelas = master_kelas_perawatan.id_kelas_perawatan
		LEFT JOIN ppk ON surat_jaminan.kd_ppk = ppk.kd_ppk
		LEFT JOIN klaim ON surat_jaminan.kd_surat_jaminan = klaim.kd_surat_jaminan
		LEFT JOIN rc_users ON surat_jaminan.id_verifikator = rc_users.id
		WHERE klaim.kd_klaim = ? AND jenis = "kontrol" LIMIT 1';
		//echo $sql;
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_diagnosa_by($params) {
		$sql = 'SELECT * FROM jaminan_diagnosa 
		INNER JOIN master_diagnosa ON jaminan_diagnosa.kd_diagnosa = master_diagnosa.kd_diagnosa
		WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_lampiran_by($params) {
		$sql = 'SELECT * FROM jaminan_lampiran WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql, $params);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function update($kd_surat_jaminan, $dataUpdate) {
		$this->db->where('kd_surat_jaminan', $id_jaminan);
		$update = $this->db->update('surat_jaminan', $dataUpdate);
		return $update;
	}

	function delete($kd_surat_jaminan) {
		$this->db->where('kd_surat_jaminan', $kd_surat_jaminan);
		$delete = $this->db->delete('surat_jaminan');
		return $delete;
	}

	function get_ppk() {
		$sql = 'SELECT * FROM ppk WHERE id_jenis_ppk <> 1';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_peserta($kd_jaminan) {
		$sql = 'SELECT nama, alamat, tempat_lahir, tgl_lahir, kd_ppk FROM peserta
		INNER JOIN penduduk ON peserta.nik = penduduk.nik
		INNER JOIN keluarga ON penduduk.kd_kk = keluarga.kd_kk
		INNER JOIN surat_jaminan ON peserta.kd_peserta = surat_jaminan.kd_peserta
		WHERE kd_surat_jaminan = ?';
		$query = $this->db->query($sql,$kd_jaminan);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_klaim_max($kode) {
		$sql = 'SELECT max(kd_klaim) as max FROM klaim 
		WHERE kd_klaim LIKE "'.$kode.'%"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['max'];
	}

	function get_klaim_baru($kode) {
		$sql = 'SELECT kd_klaim FROM klaim 
		WHERE kd_surat_jaminan = "'.$kode.'" AND jenis = "baru"';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->row_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result['kd_klaim'];
	}
}
